package com.example.fmgidsprototype.data;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.provider.BaseColumns;

import com.example.fmgidsprototype.data.CategoryTable.CategoryColumns;
import com.example.fmgidsprototype.data.VenueCategoryTable.VenueCategoryColumns;
import com.example.fmgidsprototype.data.VenueTable.VenueColumns;
import com.example.fmgidsprototype.model.Category;
import com.example.fmgidsprototype.model.Venue;

public class VenueCategoryDao {
	
	private SQLiteDatabase db;
	private SQLiteStatement insertStatement;
	private static final String INSERT = "insert into " + VenueCategoryTable.TABLE_NAME +
			"(" + VenueCategoryColumns.VENUE_ID + ", " + VenueCategoryColumns.CATEGORY_ID +
			") values (?,?)";
	
	public VenueCategoryDao(SQLiteDatabase db){
		this.db = db;
		this.insertStatement = db.compileStatement(VenueCategoryDao.INSERT);
	}
	
	public long saveKey(VenueCategoryKey key){
		insertStatement.clearBindings();
		insertStatement.bindLong(1, key.getVenueId());
		insertStatement.bindLong(2, key.getCategoryId());
		try{
			return insertStatement.executeInsert();
		}
		catch(Exception e){
			return -1;
		}
	}
	
	public long updateKey(VenueCategoryKey key){
		ContentValues values = new ContentValues();
		values.put(VenueCategoryColumns.VENUE_ID, key.getVenueId());
		values.put(VenueCategoryColumns.CATEGORY_ID, key.getCategoryId());
		try{
			return db.update(VenueCategoryTable.TABLE_NAME, values, BaseColumns._ID + " = ?", 
					new String[]{String.valueOf(key.getId())});
		}
		catch(Exception e){
			return -1;
		}
	}
	
	public List<Category> getCategoriesByVenue(long venueId){
		List<Category> list = new ArrayList<Category>();
		String query = "select " + VenueCategoryColumns.CATEGORY_ID + ", " +
				CategoryColumns.NAME + ", " + CategoryColumns.DESCRIPTION + ", " +
				CategoryColumns.ICON_NAME + " from " + VenueCategoryTable.TABLE_NAME + "," +
				CategoryTable.TABLE_NAME + " where " + VenueCategoryColumns.VENUE_ID + " = ? and " +
				VenueCategoryColumns.CATEGORY_ID + " = " + BaseColumns._ID;
		Cursor c = db.rawQuery(query, new String[]{String.valueOf(venueId)});
		if(c.moveToFirst()){
			do{
				Category category = CategoryDao.createCategoryFromCursor(c);
				list.add(category);
			}
			while(c.moveToNext());
		}
		if(!c.isClosed())
			c.close();
		return list;
	}
	
	public List<Venue> getVenuesByCategory(long categoryId){
		List<Venue> list = new ArrayList<Venue>();
		String query = "select " + VenueCategoryColumns.VENUE_ID + ", " +
				VenueColumns.NAME + ", " + VenueColumns.DESCRIPTION + ", " +
				VenueColumns.ADDRESS + ", " + VenueColumns.THUMB_NAME + ", " +
				VenueColumns.IMAGE_URL + ", " + VenueColumns.URL + ", " + 
				VenueColumns.EMAIL + ", " + VenueColumns.PHONE + ", " + 
				VenueColumns.AUDIO_PATH + ", " + VenueColumns.RATING +	
				" from " + VenueCategoryTable.TABLE_NAME + "," + VenueTable.TABLE_NAME + " where " +
				VenueCategoryColumns.CATEGORY_ID + " = ? and " + VenueCategoryColumns.VENUE_ID +
				" = " + BaseColumns._ID;
		Cursor c = db.rawQuery(query, new String[]{String.valueOf(categoryId)});
		if(c.moveToFirst()){
			do{
				Venue venue = VenueDao.createVenueFromCursor(c);
				list.add(venue);
			}
			while(c.moveToNext());
		}
		if(!c.isClosed())
			c.close();
		return list;
	}
	
	public VenueCategoryKey getKeyByVenueAndCategory(long venueId, long categoryId){
		VenueCategoryKey key = null;
		Cursor c = db.query(VenueCategoryTable.TABLE_NAME, new String[]{
				BaseColumns._ID	}, VenueCategoryColumns.VENUE_ID + " = ? and " +
				VenueCategoryColumns.CATEGORY_ID + " = ? ", new String[]{
				String.valueOf(venueId), String.valueOf(categoryId)
		}, null, null, null);
		if(c.moveToFirst()){
			key = new VenueCategoryKey();
			key.setId(c.getLong(0));
			key.setVenueId(venueId);
			key.setCategoryId(categoryId);
			return key;
		}
		return null;
	}
	
	public void deleteAll(){
		String deleteSql = "delete from " + VenueCategoryTable.TABLE_NAME ;
		db.execSQL(deleteSql);
	}
	

}
